import json

from com.arcfox.middleware import async_mysql_middleware as db


class YiQingParser:
    def __init__(self):
        pass

    async def remove_old_city_code(self):
        await db.truncate("af_city_info")

    async def remove_old_risk_area(self):
        await db.truncate("af_risk_area")

    async def save_city_code(self, data):
        if data:
            insert_sql = f"insert into af_city_info(city_code, city_name, city_key, pcode, pname, pkey) values " \
                         f"('{data['code']}', '{data['name']}', '{data['key']}', '{data['pcode']}', '{data['pname']}', '{data['pkey']}')"
            await db.save_or_update(insert_sql)

    async def save_yq_data(self, data, city_code):
        if data and data['county_name']:
            country_name = data['county_name']
            select_sql = f"select * from af_yiqing_data where country_name = '{country_name}' and city_code = '{city_code}'"
            old_data = await db.query_all(select_sql)
            has_risk = 0
            items = ''
            if data['list']:
                has_risk = 1
                items = json.dumps(data['list'], ensure_ascii=False)
            if old_data:
                sql = f"update af_yiqing_data set level_code = '{data['level_code']}', " \
                      f"level_name = '{data['level_name']}', note = '{data['note']}', has_risk = '{has_risk}'," \
                      f" items = '{items}', end_update_time = '{data['end_update_time']}', lastupdate_time = now()" \
                      f" where country_name='{country_name}' and city_code = '{city_code}'"

            else:
                sql = f"insert into af_yiqing_data(country_name, city_code, level_code, level_name, note, has_risk, " \
                      f"items, end_update_time) values ('{country_name}', '{city_code}', '{data['level_code']}', " \
                      f"'{data['level_name']}', '{data['note']}', '{has_risk}', '{items}', '{data['end_update_time']}')"
            await db.save_or_update(sql)

    def parse_city_code(self, data_list, items, pcode, pname, pkey):
        for city in items:
            code = city['code']
            name = city['name']
            key = city['key']
            data = {
                "code": code,
                "name": name,
                "pcode": pcode,
                "pname": pname,
                "pkey": pkey,
                "key": key
            }
            items = city['items']
            data_list.append(data)
            if items:
                self.parse_city_code(data_list, items, code, name, key)

    async def save_risk_area(self, data):
        end_update_time = data['end_update_time']
        high_area_list = data['highlist']
        await self.__save_risk_area_data(high_area_list, "高风险", end_update_time)
        middle_area_list = data['middlelist']
        await self.__save_risk_area_data(middle_area_list, "中风险", end_update_time)

    async def __save_risk_area_data(self, risk_area_list, level_name, end_update_time):
        for risk_area in risk_area_list:
            province = risk_area['province']
            city = risk_area['city']
            county = risk_area['county']
            area_name = risk_area['area_name']
            communitys = risk_area['communitys']
            for community in communitys:
                insert_sql = f"insert into af_risk_area(province_name, city_name, county, area_name, level_name, community, end_update_time) values " \
                             f"('{province}', '{city}', '{county}', '{area_name}', '{level_name}', '{community}', '{end_update_time}')"
                await db.save_or_update(insert_sql)
